﻿Imports DTO
Imports System.Data.SqlClient

Public Class CTheLoaiSachDAO
    Public Function LayDanhSachTheLoaiSach() As List(Of CTheLoaiSachDTO)
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("usp_LayDanhSachTheLoaiSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim listtls As New List(Of CTheLoaiSachDTO)()
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While reader.Read()
                    listtls.Add(New CTheLoaiSachDTO(reader))
                End While
            End Using
            Return listtls
        End Using
    End Function

    Public Function ThemTheLoaiSach(ByVal tentheloaisach As String) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("usp_ThemTheLoaiSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@tentheloai", tentheloaisach)
            cm.Parameters.Add(para)
            Dim parakq As New SqlParameter("@kt", 0)
            parakq.Direction = ParameterDirection.Output
            cm.Parameters.Add(parakq)
            cm.ExecuteNonQuery()
            Dim kq As Integer = Integer.Parse(cm.Parameters("@kt").Value.ToString())
            cn.Close()
            Return kq
        End Using
    End Function


    Public Function CapNhatTheLoaiSach(ByVal theloaisach As CTheLoaiSachDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("usp_CapNhatTheLoaiSach", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@matheloai", theloaisach.Ma)
            cm.Parameters.Add(para)
            para = New SqlParameter("@tentheloai", theloaisach.Ten)
            cm.Parameters.Add(para)
            Dim parakt As New SqlParameter("@kt", 0)
            parakt.Direction = ParameterDirection.Output
            cm.Parameters.Add(parakt)

            cm.ExecuteNonQuery()
            Dim kq As Integer = Integer.Parse(cm.Parameters("@kt").Value.ToString())
            cn.Close()
            Return kq
        End Using
    End Function

    Public Function LayTenTheLoaiSachTheoMa(ByVal _ma As Integer) As String
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspLayTenTheLoaiSachTheoMa", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ma", _ma)
            cm.Parameters.Add(para)
            Dim str As String = cm.ExecuteScalar()
            cn.Close()
            Return str
        End Using
    End Function

    Public Function MuonSachTheoTheLoai(ByVal thang As Integer, ByVal nam As Integer) As List(Of CTheLoaiSachDTO)
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspMuonSachTheoTheLoai", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@thang", thang)
            cm.Parameters.Add(para)
            para = New SqlParameter("@nam", nam)
            cm.Parameters.Add(para)
            Dim ltheloaisach As New List(Of CTheLoaiSachDTO)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While reader.Read()
                    ltheloaisach.Add(New CTheLoaiSachDTO(reader("MaTheLoai"), reader("SoLuong")))
                End While
            End Using
            Return ltheloaisach
        End Using
    End Function

End Class
